package com.centling.bluecollar.util;

import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * 
 * filePath: Excel文件路径;  Entity: entity Class; columnHeads: entity 类名并以Excel导入文件表头为顺序
 * 返回包含转换成entity对象的ArrayList
 * @author jeremy
 *
 */
public class ExcelOperations {
	public ArrayList<Object> readExcel(String filePath, Class entity,
			String[] columnHeads, int noReadSize) throws Exception {
		ArrayList<Method> list = new ArrayList<Method>();
		ArrayList<Object> objs = new ArrayList<Object>();

		try {
			for (int i = 0; i < columnHeads.length; i++) {
				char f = columnHeads[i].charAt(0);
				if (!Character.isUpperCase(f)) {
					columnHeads[i] = String.valueOf(Character.toUpperCase(f))
							+ columnHeads[i].substring(1);
				}
				Method methodGet = entity.getMethod("get" + columnHeads[i]);
				Method methodSet = entity.getMethod("set" + columnHeads[i], methodGet.getReturnType());
				list.add(methodSet);
			}
			InputStream inputstream = new FileInputStream(filePath);
			Workbook wb = WorkbookFactory.create(inputstream);
			Sheet sheet1 = wb.getSheetAt(0);
			for (int i = 1; i <= sheet1.getLastRowNum() - noReadSize; i++) {
				try {
					
					Object obj = entity.newInstance();
					Cell cell = null;
					for (int k = 0; k < list.size(); k ++) {												
						cell = sheet1.getRow(i).getCell(k);
						if(cell.getCellType() == 0){
						//判断是否为日期
						if(HSSFDateUtil.isCellDateFormatted(cell)){
							SimpleDateFormat sdf = null;  
			                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat  
			                        .getBuiltinFormat("h:mm")) {  
			                    sdf = new SimpleDateFormat("HH:mm");  
			                } else {// 日期  
			                    sdf = new SimpleDateFormat("yyyy-MM-dd");  
			                }  
			                Date date = cell.getDateCellValue();  
			                cell.setCellValue(sdf.format(date)); 
			             
						}else{
						        double value = cell.getNumericCellValue();  
				                CellStyle style = cell.getCellStyle();  
				                DecimalFormat format = new DecimalFormat();  
				                String temp = style.getDataFormatString();  
				                // 单元格设置成常规  
				                if (temp.equals("General")) {  
				                    format.applyPattern("#");  
				                }  
				                cell.setCellValue(format.format(value)); 
				               
						}																
						}
						list.get(k).invoke(obj, cell.toString());
					}
					objs.add(obj);
				} catch (Exception e) {
					e.printStackTrace();
					throw new Exception("Excel 文件第" + i + "行格式错误");
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		}
		return objs;
	}
	/**
	 * 去除sheet表内的空白行
	 * @param sheet
	 * @return
	 */
	public static HSSFSheet clearEmptyRowsOfHSSFSheet(HSSFSheet sheet){
		if(sheet!=null){
			CellReference cellReference = new CellReference("A4");
			boolean flag = false;
	        for (int i = cellReference.getRow(); i <= sheet.getLastRowNum();) {  
	            Row r = sheet.getRow(i);  
	            if(r == null){  
	                // 如果是空行（即没有任何数据、格式），直接把它以下的数据往上移动  
	                sheet.shiftRows(i+1, sheet.getLastRowNum(),-1);  
	                continue;  
	            }  
	            flag = false;  
	            for(Cell c:r){  
	                if(c.getCellType() != Cell.CELL_TYPE_BLANK){  
	                    flag = true;  
	                    break;  
	                }  
	            }  
	            if(flag){  
	                i++;  
	                continue;  
	            }  
	            else{//如果是空白行（即可能没有数据，但是有一定格式）  
	                if(i == sheet.getLastRowNum())//如果到了最后一行，直接将那一行remove掉  
	                    sheet.removeRow(r);  
	                else//如果还没到最后一行，则数据往上移一行  
	                    sheet.shiftRows(i+1, sheet.getLastRowNum(),-1);  
	            }  
	        }  
	        return sheet;
		}
		return null;
	}
}
